﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Way.Bus.Controllers;
using System.Data;
using Way.EAP.DataAccess.Data;
using Way.EAP.DataAccess.Regulation;
using Way.Contract.Data.ControllerData;

namespace Way.Bus.WMS.ST.Controllers.Reports
{
    class SequenceTrackController : ReportBaseController
    {
        [WhereParameter]
        public string sequencecode { set; get; }
       
        [WhereParameter]
        public string mcode { set; get; }

        [WhereParameter]
        public string customername { set; get; }

        [WhereParameter]
        public string fmodel { set; get; }

        [WhereParameter]
        public string fname { set; get; }

        [WhereParameter]
        public string warehousename { set; get; }

        [WhereParameter]
        public string hqname { set; get; }

        [WhereParameter]
        public string hwname { set; get; }

        [WhereParameter]
        public string batchno { set; get; }

        [WhereParameter]
        public string senbatchno { set; get; }

        [WhereParameter]
        public string incode { set; get; }

        [WhereParameter]
        public string outcode { set; get; }

        [WhereParameter]
        public DateTime insubmiteddate { set; get; }

        [WhereParameter]
        public DateTime outsubmiteddate { set; get; }
        //[WhereParameter]
        //public bool t3 { set; get; }
        public override System.Data.DataTable GetTable(string tableName, List<EAP.DataAccess.Regulation.SQLWhere> whereList)
        {
            /* sql 记录 
             
             select inordertb.*,infotb.warehousename,infotb.hqname,infotb.hwname,infotb.batchno,infotb.senbatchno, outordertb.outcode,outordertb.outsubmiteddate,outordertb.outsubmited from 
 (
  select inseq.sequencecode sequencecode,inmater.materialcode,goods.fname, inmater.mcode,inmater.fmodel,
        inorder.code incode,customer.customername,inorder.issubmited insubmited, 
        inorder.submiteddate insubmiteddate,worker.workname insubmituser 
           from 
               (select sequencecode,InOrderMATERIALID from Acc_WMS_InSequence where  sequencecode <> '') inseq 
                left join Acc_WMS_InOrderMaterials inmater on inseq.InOrderMATERIALID = inmater.id 
                left join Acc_WMS_InOrder inorder on inmater.parentid = inorder.id 
                left join Acc_WMS_WareHouse hw on  inmater.depotwbs = hw.id 
                left join Acc_WMS_WareHouse ck on  inorder.towhno = ck.id 
                left join Acc_Bus_OfficeWorker worker on  inorder.submitedby = worker.id 
                left join Acc_Bus_BusinessCustomer customer on inorder.clientno = customer.id
                left join Acc_Bus_BusinessCommodity goods on  inmater.materialcode = goods.id
                       where inmater.materialcode is not null
   ) inordertb 
      left join 
   (
    select outseq.sequencecode outsequencecode,outmater.stay3,outmater.stay4,outmater.stay5,
           outorder.submiteddate outsubmiteddate,outorder.code outcode,outorder.issubmited outsubmited 
              from Acc_WMS_OutInSequence outseq 
                 left join Acc_WMS_OutOrderMaterials outmater on outseq.OutOrderMATERIALID = outmater.id 
                 left join  Acc_WMS_OutOrder outorder on outmater.parentid = outorder.id
   ) outordertb 
   
      on inordertb.sequencecode = outordertb.outsequencecode
      
      left join 
      (select infoseq.sequencecode infosequencecode, infomater.* from Acc_WMS_infoInSequence infoseq,
           ( select  a.id,ck.warehousename ,hq.warehousename hqname,hw.warehousename hwname,a.batchno,a.senbatchno from Acc_WMS_InfoMaterials a
                left join  Acc_WMS_WareHouse ck on a.warehouseid=ck.id 
                left join  Acc_WMS_WareHouse hw on a.depotwbs=hw.id 
                left join  Acc_WMS_WareHouse hq on hw.parentid=hq.id           
           ) infomater  where infoseq.STOCKINFOMATERIALSID = infomater.id  and infoseq.sequencecode <> '') infotb
      on inordertb.sequencecode = infotb.infosequencecode  
             
             
             */


            string sql = " ( select inordertb.*,infotb.warehousename,infotb.hqname,infotb.hwname,infotb.batchno,infotb.senbatchno, outordertb.outcode,outordertb.outsubmiteddate,outordertb.outsubmited from ("
            + "select inseq.sequencecode sequencecode,inmater.materialcode,goods.fname, inmater.mcode,inmater.fmodel,inorder.code incode,customer.customername,inorder.issubmited insubmited,inorder.submiteddate insubmiteddate,worker.workname insubmituser"
            + " from (select sequencecode,InOrderMATERIALID from Acc_WMS_InSequence where  sequencecode <> '') inseq"
            + " left join Acc_WMS_InOrderMaterials inmater on inseq.InOrderMATERIALID = inmater.id"
            + " left join Acc_WMS_InOrder inorder on inmater.parentid = inorder.id"
            + " left join Acc_WMS_WareHouse hw on  inmater.depotwbs = hw.id"
            + " left join Acc_WMS_WareHouse ck on  inorder.towhno = ck.id"
            + " left join Acc_Bus_OfficeWorker worker on  inorder.submitedby = worker.id"
            + " left join Acc_Bus_BusinessCustomer customer on inorder.clientno = customer.id"
            + " left join Acc_Bus_BusinessCommodity goods on  inmater.materialcode = goods.id"
            + " where inmater.materialcode is not null ) inordertb"
            + " left join"
            + " (select outseq.sequencecode outsequencecode,outmater.stay3,outmater.stay4,outmater.stay5,outorder.submiteddate outsubmiteddate,outorder.code outcode,outorder.issubmited outsubmited"
            + " from Acc_WMS_OutInSequence outseq left join Acc_WMS_OutOrderMaterials outmater on outseq.OutOrderMATERIALID = outmater.id"
            + " left join  Acc_WMS_OutOrder outorder on outmater.parentid = outorder.id) outordertb"
            + " on inordertb.sequencecode = outordertb.outsequencecode"
            + "  left join (select infoseq.sequencecode infosequencecode, infomater.* from Acc_WMS_infoInSequence infoseq, ( select  a.id,ck.warehousename ,hq.warehousename hqname,hw.warehousename hwname,a.batchno,a.senbatchno from Acc_WMS_InfoMaterials a left join  Acc_WMS_WareHouse ck on a.warehouseid=ck.id left join  Acc_WMS_WareHouse hw on a.depotwbs=hw.id  left join  Acc_WMS_WareHouse hq on hw.parentid=hq.id ) infomater  where infoseq.STOCKINFOMATERIALSID = infomater.id  and infoseq.sequencecode <> '') infotb "
            + " on inordertb.sequencecode = infotb.infosequencecode "
            + ") tb";
          
            DataTable t = base.GetTable(sql, whereList);
            //IDataAction action = this.model.GetDataAction();
            //loadItem item = new loadItem();
            //item.whereList = whereList.ToArray();
            //string where=this.GetWhereString(
            //if (where != "")
            //    where = "where " + where;
            
            //string sql = "select * from v_employeekpireport " + where;
            //t = action.GetDataTable(sql);
            return t;
        }
        protected override void OnSetParam(WhereParameter param)
        {
            if (param.field == "sequencecode")
            {
                param.title = "序列号";
            }
           

            if (param.field == "mcode")
            {
                param.title = "商品编号";
            }
            if (param.field == "fmodel")
            {
                param.title = "规格型号";
            }

            if (param.field == "fname")
            {
                param.title = "商品名称";
            }

            if (param.field == "warehousename")
            {
                param.title = "仓库";
            }

            if (param.field == "batchno")
            {
                param.title = "批次";
            }

            if (param.field == "senbatchno")
            {
                param.title = "有效期";
            }

            if (param.field == "customername")
            {
                param.title = "客户名称";
            }

            if (param.field == "hqname")
            {
                param.title = "货区";
            }

            if (param.field == "hwname")
            {
                param.title = "货位";
            }

            if (param.field == "incode")
            {
                param.title = "入库单号";
            }
            if (param.field == "outcode")
            {
                param.title = "出库单号";
            }

            if (param.field == "insubmiteddate")
            {
                param.title = "入库单提交时间";
                param.wType = WhereParameter.WhereType.DateTime;
            }
            if (param.field == "outsubmiteddate")
            {
                param.title = "出库单提交时间";
                param.wType = WhereParameter.WhereType.DateTime;
            }
            //if (param.field == "outtime")
            //{
            //    param.title = "是否";
            //    param.wType = WhereParameter.WhereType.ComBox;
            //    param.comboxvalue = "[{ \"Text\": \"否\", \"Value\": 0 }, { \"Text\": \"是\", \"Value\": 1}]";
            //}
        }
    }
}
